﻿using Oracle.ManagedDataAccess.Client;
using ORAProvider;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using UserService.Entity;

namespace UserService.Repository.Repositories
{
    public class UserRepository
    {
        private readonly OracleDataContext _db;
        public UserRepository(OracleDataContext oracleContext)
        {
            _db = oracleContext;
        }

        public User GetUserById(string userId)
        {
            var result = _db.ExecuteQuery(
              "SELECT * FROM users WHERE id = :id",
              new OracleParameter { ParameterName = ":id", Value = userId, OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input });

            var row = result.Rows.Cast<DataRow>().SingleOrDefault();

            if (row != null)
            {
                return new User
                {
                    Id = row["id"].ToString(),
                    UserName = row["username"].ToString(),
                    PasswordHash = row["passwordhash"].ToString().HasValue() ? row["passwordhash"].ToString() : null,
                    SecurityStamp = row["securitystamp"].ToString().HasValue() ? row["securitystamp"].ToString() : null
                };
            }

            return null;
        }

        public ICollection<User> GetUserByUserPass(string userName, string password)
        {
            var result = _db.ExecuteQuery(
                "SELECT * FROM users WHERE username = :name",
                new OracleParameter { ParameterName = ":name", Value = userName, OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input });

            return result.Rows.Cast<DataRow>().Select(
                r => new User
                {
                    Id = r["id"].ToString(),
                    UserName = r["username"].ToString(),
                    PasswordHash = r["passwordhash"].ToString().HasValue() ? r["passwordhash"].ToString() : null,
                    SecurityStamp = r["securitystamp"].ToString().HasValue() ? r["securitystamp"].ToString() : null
                }).ToList();
        }

        public int AddUsser(User obj)
        {
            return _db.ExecuteNonQuery(
                "INSERT INTO USERS (id,username,passwordhash,securitystamp,realname,workunit,incumbentpost,presentaddress,zipcodenumber,seatnumber,phonenumber,mailbox,qqnumber ) VALUES ("
                + ":id,:username,:passwordhash,:securitystamp,:realname,:workunit,:incumbentpost,:presentaddress,:zipcodenumber,:seatnumber,:phonenumber,:mailbox,:qqnumber )",
                new OracleParameter { ParameterName = ":id", Value = obj.Id, OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input },
                new OracleParameter { ParameterName = ":username", Value = obj.UserName, OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input },
                new OracleParameter { ParameterName = ":passwordhash", Value = obj.PasswordHash, OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input },
                new OracleParameter { ParameterName = ":securitystamp", Value = obj.SecurityStamp, OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input },
                new OracleParameter { ParameterName = ":realname", Value = obj.RealName, OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input },
                new OracleParameter { ParameterName = ":workunit", Value = obj.WorkUnit, OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input },
                new OracleParameter { ParameterName = ":incumbentpost", Value = obj.IncumbentPost, OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input },
                new OracleParameter { ParameterName = ":presentaddress", Value = obj.PresentAddress, OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input },
                new OracleParameter { ParameterName = ":zipcodenumber", Value = obj.ZipCodeNumber, OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input },
                new OracleParameter { ParameterName = ":seatnumber", Value = obj.SeatNumber, OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input },
                new OracleParameter { ParameterName = ":phonenumber", Value = obj.PhoneNumber, OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input },
                new OracleParameter { ParameterName = ":mailbox", Value = obj.MailBox, OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input },
                new OracleParameter { ParameterName = ":qqnumber", Value = obj.QQNumber, OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input });
            //return false;
        }
    }
}
